using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DTO;

namespace DAO
{
    public class LopHocDao
    {
        
        public IList LayDanhSach()
        {
            ArrayList ds = new ArrayList();
            OleDbConnection cn;
            string strSQL;
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            strSQL = "Select * From LopHoc";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            LopHocDto lh = new LopHocDto();
            while (dr.Read())
            {
                lh = new LopHocDto();
                lh.Ma = (int)dr["Ma"];
                lh.Ten = (string)dr["Ten"];
                ds.Add(lh);
            }
            // B5: Dong ket noi CSDL
            cn.Close();
            return ds;
        }

        public DataTable LayBang()
        {
            DataTable dt = new DataTable();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From LopHoc";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            // B5: Dong ket noi CSDL
            cn.Close();
            return dt;
        }

        public void CapNhatBang(DataTable dt)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From LopHoc";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            da.Update(dt);
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public LopHocDto TimKiem(int maLop)
        {
            LopHocDto lhDto = new LopHocDto();
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Select * From LopHoc Where Ma = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);
            cmd.Parameters["@Ma"].Value = maLop;
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                lhDto.Ma = (int)dr["Ma"];
                lhDto.Ten = (string)dr["Ten"];
            }
            // B5: Dong ket noi CSDL
            dr.Close();
            cn.Close();
            return lhDto;
        }

        public void Them(LopHocDto lhDto)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Insert into LopHoc(Ten) values(?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ten", OleDbType.WChar);
            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            lhDto.Ma = (int)cmd.ExecuteScalar();
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public void Xoa(int maLop)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Delete From LopHoc Where Ma = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);
            cmd.Parameters["@Ma"].Value = maLop;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }

        public void Sua(LopHocDto lhDto)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            OleDbConnection cn;
            cn = DataProvider.ConnectionData();
            // B3: Tao chuoi strSQL thao tac CSDL
            string strSQL;
            strSQL = "Update LopHoc Set Ten = ? Where Ma = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Ten", OleDbType.WChar);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);
            cmd.Parameters["@Ten"].Value = lhDto.Ten;
            cmd.Parameters["@Ma"].Value = lhDto.Ma;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            cn.Close();
        }
    }
}
